Real-time Change Data Capture Using Staging Tables and Delta View Generation for Incremental Loading of Large Dimensions in a Data Warehouse

In the big data era, data become more important for Business Intelligence and Enterprise Data Analytics system operation. The load cycle of traditional data warehouse is fixed and longer, which can’t timely response the rapid and real time data change. Real-time data warehouse technology as an extension of traditional data warehouse can capture the rapid data change and process the real-time data analysis to meet the requirement of Business Intelligence and Enterprise Data Analytics system. The real-time data access without processing delay is a challenging task to the real-time data warehouse. In this paper we discusses current CDC technologies and presents the theory about why they are unable to deliver changes in real-time. This paper also explain the approaches of dimension delta view generation of incremental loading of real-time data and staging table ETL framework to process the historical data and real-time data separately. Incremental load is the preferred approach in efficient ETL processes. Delta view and stage table framework for a dimension encompasses all its source table and produces a set of keys that should be incrementally processed. We have employed this approach in real world project and have noticed an effectiveness of real-time data ETL and reduction in the loading time of big dimension. INTRODUCTION Data transformations are often the most complex and, in terms of processing time, the most costly part of the extraction, transformation, and loading (ETL) process. Extraction is the operation of extracting data from a source system for further use in a data warehouse environment. This is the first step of the ETL process. After the extraction, this data can be transformed and loaded into the data warehouse. They can range from simple data conversions to extremely complex data scrubbing techniques. The data acquisition process represents one of the most technically demanding and one of the most expensive parts of building a data warehouse. The data needed for the data warehouse are being extracted from their sources during an extract, transfer and load (ETL) process. Typical data warehouse architecture includes a data staging area where the extracted data are stored temporarily, and subsequently, upon cleaning and transformation phase, transferred into the data warehouse. Usually, the traditional data warehouse only supports the historical information query and analysis, which cannot obtain the upto-date real-time data. After the first or initial loading, data warehouse must be periodically refreshed to keep up with source data updates. Data warehouse loading is usually done periodically, by a background process. The update patterns (daily, weekly, etc.) for traditional data warehouses and data integration process result in outdated data to a greater or lesser extent. The naive approach to refreshing a data warehouse is referred to as full reloading. Possible refreshment scenario is to repeat the initial loading process using modified source data, compare the results with the current DW in order to determine changes that need to be done and finally perform the changes. This strategy is known as full DW reload. With the increasing size and complexity of DW, full reloading becomes inadequate, and in some cases inapplicable. More appropriate approach is a gradual change of DW in accordance with the changes that have occurred in the data sources since the last synchronization. Only the data that has changed since the previous reload needs to be transformed and processed. This approach is known as incremental reloading. Incremental reload work quicker and efficiently than full reload. The basic of incremental and real-time loading is that the changes of source data can be captured and later on propagated to the data warehouse. There is a number of known techniques for changed data capture. Novateur Publication’s International Journal of Innovation in Engineering, Research and Technology [IJIERT] ICITDCEME’15 Conference Proceedings